# -*- coding: utf-8 -*-

from collections import defaultdict
from datetime import timedelta, datetime, date
from dateutil.relativedelta import relativedelta
import pandas as pd
from pytz import utc
from odoo import models, fields, api, _
from odoo.http import request
from odoo.tools import float_utils
ROUNDING_FACTOR = 16

import logging
_logger = logging.getLogger(__name__)


class Employee(models.Model):
    _inherit = 'hr.employee'

    birthday = fields.Date('Date of Birth', groups="base.group_user")

    @api.model
    def get_user_employee_details(self):
        uid = request.session.uid
        employee = self.env['hr.employee'].sudo().search_read([('user_id', '=', uid)], limit=1)
        if employee and len(employee) != 0:
            payslip_count = self.env['hr.payslip'].sudo().search_count([('employee_id', '=', employee[0]['id']),('state','in',['done'])])
        else:
            payslip_count = 0
        leaves_to_approve = self.env['hr.leave'].sudo().search_count([('state', 'in', ['confirm', 'validate1'])])
        today = datetime.strftime(datetime.today(), '%Y-%m-%d')
        query = """
        select count(id)
        from hr_leave
        WHERE (hr_leave.date_from::DATE,hr_leave.date_to::DATE) OVERLAPS ('%s', '%s') and 
        state='validate'""" % (today, today)
        cr = self._cr
        cr.execute(query)
        leaves_today = cr.fetchall()
        first_day = date.today().replace(day=1)
        last_day = (date.today() + relativedelta(months=1, day=1)) - timedelta(1)
        query = """
                select count(id)
                from hr_leave
                WHERE (hr_leave.date_from::DATE,hr_leave.date_to::DATE) OVERLAPS ('%s', '%s')
                and  state='validate'""" % (first_day, last_day)
        cr = self._cr
        cr.execute(query)
        leaves_this_month = cr.fetchall()
        leaves_alloc_req = self.env['hr.leave.allocation'].sudo().search_count([('state', 'in', ['confirm', 'validate1'])])
        timesheet_count = self.env['account.analytic.line'].sudo().search_count(
            [('project_id', '!=', False), ('user_id', '=', uid)])
        timesheet_view_id = self.env.ref('hr_timesheet.hr_timesheet_line_search')
        timesheets = self.env['account.analytic.line'].sudo().search_count(
            [('project_id', '!=', False), ])
        job_applications = self.env['hr.applicant'].sudo().search_count([])
        resignation_approve = self.env['hr.resignation'].sudo().search_count([('state', 'in', ['Confirm'])])
        attendance_today = self.env['hr.attendance'].sudo().search_count([('check_in', '>=',
                            str(datetime.now().replace(hour=0, minute=0, second=0)-timedelta(hours=8))),
                            ('check_in', '<=', str(datetime.now().replace(hour=23, minute=59, second=59)-timedelta(hours=8))),'|',('employee_id.user_id', '=', uid),('employee_id.parent_id.user_id', '=', uid)])
        expenses_to_approve = self.env['hr.expense.sheet'].sudo().search_count([('state', 'in', ['submit'])])
   
        if employee:
            if employee[0]['birthday']:
                diff = relativedelta(datetime.today(), employee[0]['birthday'])
                age = diff.years
            else:
                age = False
            if employee[0]['entry_date']:
                diff = relativedelta(datetime.today(), employee[0]['entry_date'])
                years = diff.years
                months = diff.months
                days = diff.days
                experience = '{} years {} months {} days'.format(years, months, days)
            else:
                experience = False
            if employee:
                data = {
                    'payslip_count':payslip_count,
                    'leaves_to_approve': leaves_to_approve,
                    'leaves_today': leaves_today,
                    'leaves_this_month':leaves_this_month,
                    'leaves_alloc_req': leaves_alloc_req,
                    'timesheets': timesheets,
                    'emp_timesheets': timesheet_count,
                    'job_applications': job_applications,
                    'resignation_approve':resignation_approve,
                    'expenses_to_approve': expenses_to_approve,
                    'attendance_today': attendance_today,
                    'timesheet_view_id': timesheet_view_id,
                    'experience': experience,
                    'age': age
                }
                employee[0].update(data)
            return employee
        else:
            return False
    '''
    @api.model
    def get_upcoming(self):
        cr = self._cr
        uid = request.session.uid
        employee = self.env['hr.employee'].search([('user_id', '=', uid)], limit=1)

        cr.execute("""select *, 
        (to_char(dob,'ddd')::int-to_char(now(),'ddd')::int+total_days)%total_days as dif
        from (select he.id, he.name, to_char(he.birthday, 'Month dd') as birthday,
        hj.name as job_id , he.birthday as dob,
        (to_char((to_char(now(),'yyyy')||'-12-31')::date,'ddd')::int) as total_days
        FROM hr_employee he
        join hr_job hj
        on hj.id = he.job_id
        ) birth
        where (to_char(dob,'ddd')::int-to_char(now(),'DDD')::int+total_days)%total_days between 0 and 15
        order by dif;""")
        birthday = cr.fetchall()
        cr.execute("""select e.name, e.date_begin, e.date_end, rc.name as location , e.is_online 
        from event_event e
        left join res_partner rp
        on e.address_id = rp.id
        left join res_country rc
        on rc.id = rp.country_id
        where e.state ='confirm'
        and (e.date_begin >= now()
        and e.date_begin <= now() + interval '15 day')
        or (e.date_end >= now()
        and e.date_end <= now() + interval '15 day')
        order by e.date_begin """)
        event = cr.fetchall()
        announcement = []
        if employee:
            department = employee.department_id
            job_id = employee.job_id
            sql = """select ha.name, ha.announcement_reason
            from hr_announcement ha
            left join hr_employee_announcements hea
            on hea.announcement = ha.id
            left join hr_department_announcements hda
            on hda.announcement = ha.id
            left join hr_job_position_announcements hpa
            on hpa.announcement = ha.id
            where ha.state = 'approved' and 
            ha.date_start <= now()::date and
            ha.date_end >= now()::date and
            (ha.is_announcement = True or
            (ha.is_announcement = False
            and ha.announcement_type = 'employee'
            and hea.employee = %s)""" % employee.id
            if department:
                sql += """ or
                (ha.is_announcement = False and
                ha.announcement_type = 'department'
                and hda.department = %s)""" % department.id
            if job_id:
                sql += """ or
                (ha.is_announcement = False and
                ha.announcement_type = 'job_position'
                and hpa.job_position = %s)""" % job_id.id
            sql += ')'
            cr.execute(sql)
            announcement = cr.fetchall()
        return {
            'birthday': birthday,
            'event': event,
            'announcement': announcement
        }
    '''
    @api.model
    def get_dept_employee(self):
        cr = self._cr
        cr.execute("""select department_id, hr_department.name,count(*) 
from hr_employee join hr_department on hr_department.id=hr_employee.department_id where hr_department.active
group by hr_employee.department_id,hr_department.name""")
        dat = cr.fetchall()
        data = []
        for i in range(0, len(dat)):
            data.append({'label': dat[i][1], 'value': dat[i][2]})
        return data


    @api.model
    def get_department_leave(self):
        month_list = []
        graph_result = []
        for i in range(5, -1, -1):
            last_month = datetime.now() - relativedelta(months=i)
            text = format(last_month, '%B %Y')
            month_list.append(text)
        self.env.cr.execute("""select id, name from hr_department""")
        departments = self.env.cr.dictfetchall()
        department_list = [x['name'] for x in departments]
        leave_dep = {dep['name']:0 for dep in departments}
        leave_mon = {month:0 for month in month_list}
        for month in month_list:
            leave = {}
            for dept in departments:
                leave[dept['name']] = 0
            vals = {
                'l_month': month,
                'leave': leave
            }
            graph_result.append(vals)
        sql = """
        SELECT h.id, h.employee_id,h.department_id
             , extract('month' FROM y)::int AS leave_month
             , to_date(y::text, 'YYYY-MM-DD') as month_year
             , GREATEST(y                    , h.date_from) AS date_from
             , LEAST   (y + interval '1 month', h.date_to)   AS date_to
        FROM  (select * from hr_leave where state = 'validate') h
             , generate_series(date_trunc('month', date_from::timestamp)
                             , date_trunc('month', date_to::timestamp)
                             , interval '1 month') y
        where date_trunc('month', GREATEST(y , h.date_from)) >= date_trunc('month', now()) - interval '6 month' and
        date_trunc('month', GREATEST(y , h.date_from)) <= date_trunc('month', now())
        and h.department_id is not null
        """
        self.env.cr.execute(sql)
        results = self.env.cr.dictfetchall()
        leave_lines = []
        for line in results:
            employee = self.browse(line['employee_id'])
            from_dt = fields.Datetime.from_string(line['date_from'])
            to_dt = fields.Datetime.from_string(line['date_to'])
            days = employee.get_work_days_dashboard(from_dt, to_dt)
            line['days'] = days
            vals = {
                'department': line['department_id'],
                'l_month': line['month_year'],
                'days': days
            }
            leave_lines.append(vals)
        if leave_lines:
            df = pd.DataFrame(leave_lines)
            rf = df.groupby(['l_month', 'department']).sum()
            rf1 = df.groupby(['l_month']).sum()
            result_lines = rf1.to_dict('index')
            for month in month_list:
                for line in result_lines:
                #for key, value in result_lines:
                    mont = format(line, '%B %Y').replace(' ', '')
                    if month.replace(' ', '') == mont:
                        leave_mon.update({month:result_lines[line]['days']})

            rf2 = df.groupby(['department']).sum()
            result_lines = rf2.to_dict('index')
            for dep in departments:
                for line in result_lines:
                    if line == dep['id']:
                        leave_dep[dep['name']] = result_lines[line]['days']
        return month_list, list(leave_mon.values()),department_list,list(leave_dep.values())

        '''    result_lines = rf.to_dict('index')
            for month in month_list:
                for line in result_lines:
                    if month.replace(' ', '') == format(line[0], '%B %Y').replace(' ', ''):
                        match = list(filter(lambda d: d['l_month'] in [month], graph_result))[0]['leave']
                        dept_name = self.env['hr.department'].browse(line[1]).name
                        match[dept_name] = result_lines[line]['days']
        for result in graph_result:
            result['l_month'] = result['l_month'].split(' ')[:1][0].strip()[:3] + " " + result['l_month'].split(' ')[1:2][0]
        return graph_result, department_list '''

    def get_work_days_dashboard(self, from_datetime, to_datetime, compute_leaves=False, calendar=None, domain=None):
        resource = self.resource_id
        calendar = calendar or self.resource_calendar_id

        if not from_datetime.tzinfo:
            from_datetime = from_datetime.replace(tzinfo=utc)
        if not to_datetime.tzinfo:
            to_datetime = to_datetime.replace(tzinfo=utc)
        from_full = from_datetime - timedelta(days=1)
        to_full = to_datetime + timedelta(days=1)
        intervals = calendar._attendance_intervals(from_full, to_full, resource)
        day_total = defaultdict(float)
        for start, stop, meta in intervals:
            day_total[start.date()] += (stop - start).total_seconds() / 3600
        if compute_leaves:
            intervals = calendar._work_intervals(from_datetime, to_datetime, resource, domain)
        else:
            intervals = calendar._attendance_intervals(from_datetime, to_datetime, resource)
        day_hours = defaultdict(float)
        for start, stop, meta in intervals:
            day_hours[start.date()] += (stop - start).total_seconds() / 3600
        days = sum(
            float_utils.round(ROUNDING_FACTOR * day_hours[day] / day_total[day]) / ROUNDING_FACTOR
            for day in day_hours
        )
        return days

    @api.model
    def employee_leave_trend(self):
        leave_lines = []
        month_list = []
        graph_result = []
        for i in range(5, -1, -1):
            last_month = datetime.now() - relativedelta(months=i)
            text = format(last_month, '%B %Y')
            month_list.append(text)
        uid = request.session.uid
        employee = self.env['hr.employee'].sudo().search_read([('user_id', '=', uid)], limit=1)
        for month in month_list:
            vals = {
                'l_month': month,
                'leave': 0
            }
            graph_result.append(vals)
        sql = """
                SELECT h.id, h.employee_id
                     , extract('month' FROM y)::int AS leave_month
                     , to_date(y::text, 'YYYY-MM-DD') as month_year
                     , GREATEST(y                    , h.date_from) AS date_from
                     , LEAST   (y + interval '1 month', h.date_to)   AS date_to
                FROM  (select * from hr_leave where state = 'validate') h
                     , generate_series(date_trunc('month', date_from::timestamp)
                                     , date_trunc('month', date_to::timestamp)
                                     , interval '1 month') y
                where date_trunc('month', GREATEST(y , h.date_from)) >= date_trunc('month', now()) - interval '6 month' and
                date_trunc('month', GREATEST(y , h.date_from)) <= date_trunc('month', now())
                and h.employee_id = %s
                """
        self.env.cr.execute(sql, (employee[0]['id'],))
        results = self.env.cr.dictfetchall()
        for line in results:
            employee = self.browse(line['employee_id'])
            from_dt = fields.Datetime.from_string(line['date_from'])
            to_dt = fields.Datetime.from_string(line['date_to'])
            days = employee.get_work_days_dashboard(from_dt, to_dt)
            line['days'] = days
            vals = {
                'l_month': line['month_year'],
                'days': days
            }
            leave_lines.append(vals)
        if leave_lines:
            df = pd.DataFrame(leave_lines)
            rf = df.groupby(['l_month']).sum()
            result_lines = rf.to_dict('index')
            for line in result_lines:
                match = list(filter(lambda d: d['l_month'].replace(' ', '') == format(line, '%B %Y').replace(' ', ''), graph_result))
                match[0]['leave'] = result_lines[line]['days']
        for result in graph_result:
            result['l_month'] = result['l_month'].split(' ')[:1][0].strip()[:3] + " " + result['l_month'].split(' ')[1:2][0]
        return graph_result

    @api.model
    def join_resign_trends(self):
        cr = self._cr
        month_list = []
        join_trend = []
        resign_trend = []
        for i in range(11, -1, -1):
            last_month = datetime.now() - relativedelta(months=i)
            text = format(last_month, '%B %Y')
            month_list.append(text)
        for month in month_list:
            vals = {
                'l_month': month,
                'count': 0
            }
            join_trend.append(vals)
        for month in month_list:
            vals = {
                'l_month': month,
                'count': 0
            }
            resign_trend.append(vals)
        
        cr.execute('''select entry_date as l_month, count(id) from hr_employee 
        WHERE entry_date BETWEEN CURRENT_DATE - INTERVAL '12 months'
        AND CURRENT_DATE + interval '1 month - 1 day'
        group by l_month;''')
        join_data = cr.fetchall()
        cr.execute('''select resign_date as l_month, count(id) from hr_employee 
        WHERE resign_date BETWEEN CURRENT_DATE - INTERVAL '12 months'
        AND CURRENT_DATE + interval '1 month - 1 day'
        group by l_month;''')
        resign_data = cr.fetchall()

        for line in join_data:
            match = list(filter(lambda d: d['l_month'].replace(' ', '') == format(line[0], '%B %Y').replace(' ', ''), join_trend))
            match[0]['count'] += line[1]
        for line in resign_data:
            match = list(filter(lambda d: d['l_month'].replace(' ', '') == format(line[0], '%B %Y').replace(' ', ''), resign_trend))
            match[0]['count'] += line[1]
        for join in join_trend:
            join['l_month'] = join['l_month'].split(' ')[:1][0].strip()[:3]
        for resign in resign_trend:
            resign['l_month'] = resign['l_month'].split(' ')[:1][0].strip()[:3]
        graph_result = [{
            'name': _('Join'),
            'values': join_trend
        }, {
            'name': _('Resign'),
            'values': resign_trend
        }]
        return graph_result

    @api.model
    def get_attrition_rate(self):
        month_attrition = []
        monthly_join_resign = self.join_resign_trends()
        month_join = monthly_join_resign[0]['values']
        month_resign = monthly_join_resign[1]['values']
        sql = """
        SELECT (date_trunc('month', CURRENT_DATE))::date - interval '1' month * s.a AS month_start
        FROM generate_series(0,11,1) AS s(a);"""
        self._cr.execute(sql)
        month_start_list = self._cr.fetchall()
        for month_date in month_start_list:
            self._cr.execute("""select count(id), to_date('%s', 'YYYY-MM-DD') as l_month from hr_employee
            where (resign_date> date '%s' or resign_date is null) and entry_date < date '%s'
            """ % (month_date[0], month_date[0], month_date[0],))
            month_emp = self._cr.fetchone()
            # month_emp = (month_emp[0], month_emp[1].split(' ')[:1][0].strip()[:3])
            match_join = list(filter(lambda d: d['l_month'] == format(month_emp[1], '%B %Y').split(' ')[:1][0].strip()[:3], month_join))[0]['count']
            match_resign = list(filter(lambda d: d['l_month'] == format(month_emp[1], '%B %Y').split(' ')[:1][0].strip()[:3], month_resign))[0]['count']
            
            month_avg = (month_emp[0]+match_join-match_resign+month_emp[0])/2
            attrition_rate = (match_resign/month_avg)*100 if month_avg != 0 else 0
            vals = {
                # 'month': month_emp[1].split(' ')[:1][0].strip()[:3] + ' ' + month_emp[1].split(' ')[-1:][0],
                'month': format(month_emp[1], '%B %Y').split(' ')[:1][0].strip()[:3],
                'attrition_rate': round(float(attrition_rate), 2)
            }
            month_attrition.append(vals)
        return month_attrition

    @api.model
    def get_attend_trends(self):
        cr = self._cr
        uid = request.session.uid
        month_list = []
        attend_trend = []
        overtime_trend = []
        for i in range(5, -1, -1):
            last_month = datetime.now() - relativedelta(months=i)
            text = format(last_month, '%B %Y')
            month_list.append(text)
        for month in month_list:
            vals = {
                'l_month': month,
                'count': 0
            }
            attend_trend.append(vals)
        for month in month_list:
            vals = {
                'l_month': month,
                'count': 0
            }
            overtime_trend.append(vals)
        
        cr.execute('''select ha.check_in as l_month,sum(case when (late_come>0 and early_go >0) then 2 else 1 end ) from hr_attendance  ha join hr_employee he on ha.employee_id = he.id
        WHERE ha.attend_confirmed is true and he.user_id = '%s' and (ha.late_come >0 or ha.early_go >0 ) and ha.check_in BETWEEN CURRENT_DATE - INTERVAL '6 months'
        AND CURRENT_DATE + interval '1 month - 1 day' group by l_month;''' % uid)
        attend_data = cr.fetchall()
        cr.execute('''select hol.start_date as l_month, sum(hol.end_date - hol.start_date+1) from hr_overtime ho
        join hr_overtime_line  hol
        on ho.id = hol.overtime_id 
        WHERE ho.employee_id IN (select id from hr_employee where user_id = '%s') and hol.start_date BETWEEN CURRENT_DATE - INTERVAL '6 months'
        AND CURRENT_DATE + interval '1 month - 1 day'
        group by l_month;''' % uid)
        overtime_data = cr.fetchall()


        for line in attend_data:
            match = list(filter(lambda d: d['l_month'].replace(' ', '') == format(line[0], '%B %Y').replace(' ', ''), attend_trend))
            match[0]['count'] += line[1]
        for line in overtime_data:
            match = list(filter(lambda d: d['l_month'].replace(' ', '') == format(line[0], '%B %Y').replace(' ', ''), overtime_trend))
            match[0]['count'] += line[1]
        for attend in attend_trend:
            attend['l_month'] = attend['l_month'].split(' ')[:1][0].strip()[:3]
        for overtime in overtime_trend:
            overtime['l_month'] = overtime['l_month'].split(' ')[:1][0].strip()[:3]
        attend_result = [{
            'name': 'attend',
            'values': attend_trend
        }, {
            'name': 'overtime',
            'values': overtime_trend
        }]
        return attend_result


    @api.model
    def get_payslip(self):
        #cr = self._cr
        month_list = []
        payslip_trend = []
        for i in range(11, -1, -1):
            last_month = datetime.now() - relativedelta(months=i)
            text = format(last_month, '%B %Y')
            month_list.append(text)
        for month in month_list:
            vals = {
                'l_month': month,
                'count': 0
            }
            payslip_trend.append(vals)

        self.env.cr.execute('''select p.date_from as l_month,sum(pl.amount) as Total from hr_payslip p
            INNER JOIN hr_payslip_line pl on (p.id = pl.slip_id and pl.code = 'TOPAY' and p.state = 'done') 
            WHERE p.date_from BETWEEN CURRENT_DATE - INTERVAL '12 months' AND CURRENT_DATE + interval '1 month - 1 day'
            group by l_month, p.date_from order by p.date_from;''')
        payslip_data = self.env.cr.fetchall()

        for line in payslip_data:
            match = list(filter(lambda d: d['l_month'].replace(' ', '') == format(line[0], '%B %Y').replace(' ', ''), payslip_trend))
            match[0]['count'] += line[1]
        for payslip in payslip_trend:
            payslip['l_month'] = payslip['l_month'].split(' ')[:1][0].strip()[:3]
            payslip['count'] = round(payslip['count']/10000.0,2)
        graph_result = [{
            'name': 'payslip',
            'values': payslip_trend
        }]
        return graph_result


    @api.model
    def get_employees(self):
        query = """
            select e.name as employee, e.barcode as badge_id, j.name as job, d.name as department,
            e.work_phone, e.work_email, e.work_location, e.gender, e.birthday, e.marital, e.passport_id,
            e.medic_exam from hr_employee e left join hr_job j on (j.id = job_id)
            inner join hr_department d on (e.department_id = d.id)

        """
        self.env.cr.execute(query)
        employee_table = self.env.cr.dictfetchall()
        return employee_table
            